﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Web.Http;

namespace Waiyun.WebApi
{
    public class DGController : ApiController
    {
        public static string ConnectionString;

        static DGController()
        {
            ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["waiyun"].ConnectionString;
        }


        [HttpGet]
        public SingleResult<long> Total(string name)
        {
            if (string.Equals(name, "order"))
            {
                return TotalOrder();
            }
            else if (string.Equals(name, "cust"))
            {
                return TotalCustomer();
            }
            return new SingleResult<long>(0);
        }

        private SingleResult<long> TotalOrder()
        {
            string name = "dg\\total\\order";
            if (ThreadPools.Get(name) == null)
            {
                Thread thread = new Thread(() =>
                {
                    string errorMessage = null;
                    OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
                    if (connection == null)
                    {
                        return;
                    }
                    try
                    {
                        long value = 0;
                        using (OracleCommand command = new OracleCommand(@"select count(1) as total from (
select d.dfos_bl_no
from df_order_status d
where d.create_time > sysdate-30 and d.DFOS_STATUS_CODE = 'OCVLD'
minus
select d.dfos_bl_no
from df_order_status d
where d.create_time > sysdate-30 and d.DFOS_STATUS_CODE = 'OCVDS')", connection))
                        {
                            using (OracleDataReader reader = command.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    value = Convert.ToInt64(reader["total"]);
                                }
                                reader.Close();
                                Cache2.Set(name, value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return;
                    }
                    finally
                    {
                        ConnectionPool.Release(connection);
                    }
                    ThreadPools.Stop(name);
                });
                thread.Name = name;
                ThreadPools.Start(thread);
            }
            object cacheValue = Cache2.Get(name);
            if (cacheValue != null)
            {
                return new SingleResult<long>(Convert.ToInt64(cacheValue));
            }
            return new SingleResult<long>(0);

//            using (OracleConnection connection = new OracleConnection(ConnectionString))
//            {
//                try
//                {
//                    connection.Open();

//                    long value = 0;
//                    using (OracleCommand command = new OracleCommand(@"select count(1) as total from (
//select d.dfos_bl_no
//from df_order_status d
//where d.create_time > sysdate-30 and d.DFOS_STATUS_CODE = 'OCVLD'
//minus
//select d.dfos_bl_no
//from df_order_status d
//where d.create_time > sysdate-30 and d.DFOS_STATUS_CODE = 'OCVDS')", connection))
//                    {
//                        using (OracleDataReader reader = command.ExecuteReader())
//                        {
//                            if (reader.Read())
//                            {
//                                value = Convert.ToInt64(reader["total"]);
//                            }
//                            reader.Close();
//                            return new SingleResult<long>(value);
//                        }
//                    }
//                }
//                catch (Exception ex)
//                {
//                    return new SingleResult<long>(1, ex.Message);
//                }
//                finally
//                {
//                    connection.Close();
//                }
//            }
        }
        public SingleResult<long> TotalCustomer()
        {
            string errorMessage = null;
            OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
            if (connection == null)
            {
                return new SingleResult<long>(1, errorMessage);
            }
            try
            {
                long value = 0;
                using (OracleCommand command = new OracleCommand(string.Format(@"select count(1) 
from df_order_status d,df_order_status_head h
where d.create_time > TO_DATE('{0}', 'YYYY-MM-DD') and d.DFOS_ORDER_ID = h.DFOH_ORDER_ID and d.DFOS_STATUS_CODE = 'OCARI' and h.DFOH_STANDARD_CUST_ID in ('ESDY00101122','ESDY00100247','ESDH00001238','ESDY00101058','ELFD00000012','ESDY00100639','ETJF00001592','ESDY00101572','ETJF00001213','ETJF00001177')", DateTime.Now.ToString("yyyy-MM-dd")), connection))
                {
                    using (OracleDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            value = Convert.ToInt64(reader[0]);
                        }
                        reader.Close();
                        return new SingleResult<long>(value);
                    }
                }
            }
            catch (Exception ex)
            {
                return new SingleResult<long>(1, ex.Message);
            }
            finally
            {
                ConnectionPool.Release(connection);
            }
//            using (OracleConnection connection = new OracleConnection(ConnectionString))
//            {
//                try
//                {
//                    connection.Open();

//                    long value = 0;
//                    using (OracleCommand command = new OracleCommand(string.Format(@"select count(1) 
//from df_order_status d,df_order_status_head h
//where d.create_time > TO_DATE('{0}', 'YYYY-MM-DD') and d.DFOS_ORDER_ID = h.DFOH_ORDER_ID and d.DFOS_STATUS_CODE = 'OCARI' and h.DFOH_STANDARD_CUST_ID in ('ESDY00101122','ESDY00100247','ESDH00001238','ESDY00101058','ELFD00000012','ESDY00100639','ETJF00001592','ESDY00101572','ETJF00001213','ETJF00001177')", DateTime.Now.ToString("yyyy-MM-dd")), connection))
//                    {
//                        using (OracleDataReader reader = command.ExecuteReader())
//                        {
//                            if (reader.Read())
//                            {
//                                value = Convert.ToInt64(reader[0]);
//                            }
//                            reader.Close();
//                            return new SingleResult<long>(value);
//                        }
//                    }
//                }
//                catch (Exception ex)
//                {
//                    return new SingleResult<long>(1, ex.Message);
//                }
//                finally
//                {
//                    if (connection.State == System.Data.ConnectionState.Open)
//                    {
//                        connection.Close();
//                    }
//                }
//            }
        }

        [HttpGet]
        public SingleResult<Data<string, long>> XY(string name)
        {
            if (string.Equals(name, "order", StringComparison.CurrentCultureIgnoreCase))
            {
                return XYOrder();
            }
            return new SingleResult<Data<string, long>>(1, "unknown");
        }

        private SingleResult<Data<string, long>> XYOrder()
        {
            Data<string, long> cacheData = Cache.Get("DG-XYORDER") as Data<string, long>;
            if (cacheData != null)
            {
                return new SingleResult<Data<string, long>>(cacheData);
            }

            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                try
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(string.Format(@"select  count(1) as Y,to_char(CREATE_TIME,'yyyymmddhh24') as X from df_order_status 
where to_char(CREATE_TIME,'yyyymmdd')='{0}' and DFOS_STATUS_CODE = 'OCARI'
group by to_char(CREATE_TIME,'yyyymmddhh24')
　　order by to_char(CREATE_TIME,'yyyymmddhh24')", DateTime.Now.ToString("yyyyMMdd")), connection))
                    {
                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            DataTable dataTable = new DataTable();
                            adapter.Fill(dataTable);

                            Data<string, long> data = new Data<string, long>();
                            for (int index = 0; index < dataTable.Rows.Count; index++)
                            {
                                data.XValues.Add(Convert.ToString(dataTable.Rows[index]["X"]));
                                data.YValues.Add(Convert.ToInt64(dataTable.Rows[index]["Y"]));
                            }

                            Cache.Set("DG-XYORDER", data);
                            return new SingleResult<Data<string, long>>(data);
                        }
                    }
                }
                catch (Exception ex)
                {
                    return new SingleResult<Data<string, long>>(1, ex.Message);
                }
                finally
                {
                    if (connection.State == System.Data.ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }
        }
    }
}
